package com.cqndt.disaster.device.equip;

import java.io.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import com.cqndt.disaster.device.dao.ExcelMapper;
import com.cqndt.disaster.device.domain.*;
import io.swagger.annotations.Api;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@RestController
@RequestMapping("/excel")
@Api(value = "导入表", description = "导入表")
@Slf4j
public class ExcelController {
    @Autowired
    private ExcelMapper excelMapper;

    @Transactional
    @GetMapping("insert")
    public void excel() {
        Workbook wb =null;
        Sheet sheet = null;
        Row row = null;
        List<Excel> list = null;
        Set<ExcelProject> excelProjectList = new HashSet<>();//所有项目
        Set<ExcelBasic> excelBasicList = new HashSet<>();//所有灾害点
        Set<ExcelDevice> excelDeviceList = new HashSet<>();//所有设备
        Set<ExcelMonitor> excelMonitorList = new HashSet<>();//所有监测点
        Set<ExcelSensor> excelSensorList = new HashSet<>();//所有传感器
        Set<ExcelDeviceInstall> excelDeviceInstallList = new HashSet<>();//所有安装信息
        Set<ExcelDeviceType> excelDeviceTypeSet = new HashSet<>();//所有安装信息
        String cellData = null;
        //Excel文件路径
//        String filePath = "C:\\Users\\宁俊豪\\Desktop\\一二级监测\\总计 -南江（2级）.xlsx";
        String filePath = "D:\\地研院-设备总计.xlsx";
        //读取Excel文件
        wb = readExcel(filePath);
        //如果文件不为空
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<Excel>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            //循环行
            for (int i = 1; i<rownum; i++) {
                Excel country = new Excel();
                row = sheet.getRow(i);
                if(row !=null){
                    //循环列
                    for (int j=0;j<colnum;j++){
                        cellData = (String) getCellFormatValue(row.getCell(j));
                        switch (j){
                            case 0://所属区县
                                country.setAreaName(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 1://项目编号
                                country.setProjectNo(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 2://项目名称
                                country.setProjectName(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 3://项目地址
                                country.setProjectAddress(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 4://灾害点编号
                                country.setDisNo(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 5://灾害名称
                                country.setDisName(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 6://灾害类型
                                switch (cellData){
                                    case "滑坡":
                                        country.setDisType("1");
                                        break;
                                    case "泥石流":
                                        country.setDisType("2");
                                        break;
                                    case "地面沉降":
                                        country.setDisType("3");
                                        break;
                                    case "不稳定斜坡":
                                        country.setDisType("4");
                                        break;
                                    case "地面塌陷":
                                        country.setDisType("5");
                                        break;
                                    case "地裂缝":
                                        country.setDisType("6");
                                        break;
                                    case "崩塌":
                                        country.setDisType("7");
                                        break;
                                }
                                country.setDisasterName(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 7://险情等级(A特大型B大型C中型D小型),
                                switch (cellData){
                                    case "特大型":
                                        country.setDangerLevel("A");
                                        break;
                                    case "大型":
                                        country.setDangerLevel("B");
                                        break;
                                    case "中型":
                                        country.setDangerLevel("C");
                                        break;
                                    case "小型":
                                        country.setDangerLevel("D");
                                        break;
                                    default:
                                        country.setDangerLevel(null);
                                        break;
                                }
                                break;
                            case 8://监测开始时间
                                if(!StringUtils.isEmpty(cellData)){
                                    try {
                                        DateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
                                        country.setStartTime(format1.parse(cellData));
                                    } catch (ParseException e) {
                                        e.printStackTrace();
                                    }
                                }else {
                                    country.setSgrq(new Date());
                                }
                                break;
                            case 9://监测单位绑定tab_unit表unit_name，存入id
                                country.setMonitorUnit(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 10://设备状态
                                country.setIsOnline(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 11://项目X坐标

                                break;
                            case 12://项目Y坐标

                                break;
                            case 13://项目经度（2000坐标）
                                country.setProjectLon(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 14://项目纬度（2000坐标）
                                country.setProjectLat(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 15://项目联系人
                                country.setProjectMan(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 16://项目联系人联系方式
                                country.setProjectManPhone(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 17://设备名称
                                country.setDeviceName(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 18://监测点编号
                                country.setMonitorNo(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 19://设备编号
                                country.setDeviceNo(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 20://设备类型对应static_type的static_keyVal
                                country.setDeviceType(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 21://设备型号

                                break;
                            case 22://设备厂商
                                country.setDeviceFactory(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 23://监测频率

                                break;
                            case 24://设备注册时间

                                break;
                            case 25://监测位置
                                country.setPosition(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 26://设备经度,监测点经度
                                country.setDeviceLon(StringUtils.isEmpty(cellData)?null:cellData);
                                country.setMonitorLon(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 27://设备纬度,监测点纬度
                                country.setDeviceLat(StringUtils.isEmpty(cellData)?null:cellData);
                                country.setMonitorLat(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 28://监测人

                                break;
                            case 29://sim卡号
                                country.setSimCard(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 30://传输方式
                                country.setTransferType(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 31://安装位置
                                country.setAnwz(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 32://施工条件
                                country.setSgtj(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 33://供电方式
                                country.setGdfs(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 34://供电说明
                                country.setGdsm(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 35://岩性条件
                                country.setYxtj(StringUtils.isEmpty(cellData)?0:cellData.charAt(0));
                                break;
                            case 36://采光条件
                                country.setCgtj(StringUtils.isEmpty(cellData)?0:cellData.charAt(0));
                                break;
                            case 37://移动信号
                                country.setYdxh(StringUtils.isEmpty(cellData)?0:cellData.charAt(0));
                                break;
                            case 38://GPRS信号
                                country.setGprsXh(StringUtils.isEmpty(cellData)?0:cellData.charAt(0));
                                break;
                            case 39://电信3G
                                country.setDx3g(StringUtils.isEmpty(cellData)?0:cellData.charAt(0));
                                break;
                            case 40://水流情况
                                country.setSlqk(StringUtils.isEmpty(cellData)?0:cellData.charAt(0));
                                break;
                            case 41://施工日期
                                if(!StringUtils.isEmpty(cellData)){
                                    try {
                                        DateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
                                        country.setSgrq(format1.parse(cellData));
                                    } catch (ParseException e) {
                                        e.printStackTrace();
                                    }
                                }else {
                                    country.setSgrq(new Date());
                                }
                                break;
                            case 42://竣工日期
                                if(!StringUtils.isEmpty(cellData)){
                                    try {
                                        DateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
                                        country.setJgrq(format1.parse(cellData));
                                    } catch (ParseException e) {
                                        e.printStackTrace();
                                    }
                                }else {
                                    country.setSgrq(new Date());
                                }
                                break;
                            case 43://施工人员
                                country.setSgry(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 44://检查人员
                                country.setJcry(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 45://验收人员
                                country.setYsry(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 46://外观检查
                                country.setWgjc(StringUtils.isEmpty(cellData)?0:cellData.charAt(0));
                                break;
                            case 47://立杆安装
                                country.setLgaz(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 48://太阳能供电
                                country.setTyngd(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 49://数据采集
                                country.setSjcj(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 50://数据传输
                                country.setSjcs(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 51://安装单位
                                country.setAzdw(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 52://备注
                                country.setRemark(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 53://现场图片

                                break;
                            case 54://传感器类型
                                country.setSensorType(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 55://名称
                                country.setSensorName(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 56://编号
                                country.setSensorNo(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 57://数据字段名称
                                country.setType(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                            case 58://字段类型

                                break;
                            case 59://单位

                                break;
                            case 60://设备参数

                                break;
                            case 61://设备绑定用户
                                country.setUserId(StringUtils.isEmpty(cellData)?null:cellData);
                                break;
                        }
                    }

                    //放入项目
                    ExcelProject excelProject = addProject(country);
                    //放入灾害点
                    ExcelBasic excelBasic = addBasic(country);
                    //放入设备及用户绑定设备
                    ExcelDevice excelDevice = addDevice(country);
                    //放入监测点
                    ExcelMonitor excelMonitor = addMonitor(country);
                    //放入传感器
                    ExcelSensor excelSensor = addSensor(country);
                    //放入安装信息
                    ExcelDeviceInstall excelDeviceInstall = addDeviceInstall(country);
                    //放入刻度
                    ExcelDeviceType excelDeviceType = addDeviceType(country);
                    //放入集合
                    list.add(country);
                    //放入项目集合
                    excelProjectList.add(excelProject);
                    //放入灾害点集合
                    excelBasicList.add(excelBasic);
                    //放入设备集合
                    excelDeviceList.add(excelDevice);
                    //放入监测点集合
                    excelMonitorList.add(excelMonitor);
                    //放入传感器集合
                    excelSensorList.add(excelSensor);
                    //放入安装信息集合
                    excelDeviceInstallList.add(excelDeviceInstall);
                    //放入刻度集合
                    excelDeviceTypeSet.add(excelDeviceType);
                }else{
                    break;
                }
            }
        }
        //遍历解析出来的list
        for (Excel country : list) {
//            country.setSensorDeviceNo(country.getDeviceNo());//设置传感器表的设备编号
            System.out.println(country.toString());
        }
        System.out.println("------------------------------------------------------------------------------------项目------------------------------------------------------------------------------------");
//        for(ExcelProject excelProject : excelProjectList){
//            excelMapper.insertProject(excelProject);
//
//            //用户绑定项目
//            ExcelUserProject excelUserProject = new ExcelUserProject();
//            excelUserProject.setUserId(Integer.parseInt(excelProject.getUserId()));
//            excelUserProject.setProjectId(Integer.parseInt(excelProject.getId()));
//            excelMapper.insertUserProject(excelUserProject);
//
//            System.out.println(excelProject.toString());
//        }
//        System.out.println("-----------------------------------------------------------------------------------灾害点-----------------------------------------------------------------------------------");
//        for(ExcelBasic excelBasic : excelBasicList){
//            excelMapper.insertBasic(excelBasic);
//            System.out.println(excelBasic.toString());
//        }
        System.out.println("------------------------------------------------------------------------------------设备------------------------------------------------------------------------------------");
        for(ExcelDevice excelDevice : excelDeviceList){
            excelMapper.insertDevice(excelDevice);

            //用户绑定设备
            ExcelUserDevice excelUserDevice = new ExcelUserDevice();
            excelUserDevice.setUserId(Integer.parseInt(excelDevice.getUserId()));
            excelUserDevice.setDeviceId(excelDevice.getId());
            excelMapper.insertUserDevice(excelUserDevice);

            System.out.println(excelDevice.toString());
        }
        System.out.println("-----------------------------------------------------------------------------------监测点-----------------------------------------------------------------------------------");
        for(ExcelMonitor excelMonitor : excelMonitorList){

            excelMonitor.setDeviceId(excelMapper.selectDeviceNo(excelMonitor.getDeviceNo()));
            excelMonitor.setProjectId(excelMapper.selectProjectNo(excelMonitor.getProjetcNo()));
            excelMapper.insertMonitor(excelMonitor);

            System.out.println(excelMonitor.toString());
        }
        System.out.println("-----------------------------------------------------------------------------------传感器-----------------------------------------------------------------------------------");
        for(ExcelSensor excelSensor : excelSensorList){
            excelMapper.insertSensor(excelSensor);
            System.out.println(excelSensor.toString());
        }
        System.out.println("--------------------------------------------------------------------------------设备安装信息--------------------------------------------------------------------------------");
        for(ExcelDeviceInstall excelDeviceInstall : excelDeviceInstallList){
            excelMapper.insertDeviceInstall(excelDeviceInstall);
            System.out.println(excelDeviceInstall.toString());
        }

        for(ExcelDeviceType excelDeviceType : excelDeviceTypeSet){
            excelMapper.insertDeviceType(excelDeviceType);
            System.out.println(excelDeviceType.toString());
        }

    }

    //添加刻度
    private ExcelDeviceType addDeviceType(Excel country) {
        ExcelDeviceType excelDeviceType = new ExcelDeviceType();
        excelDeviceType.setDeviceType(Integer.parseInt(country.getSensorType()));
        excelDeviceType.setMonitorNo(country.getSensorNo());
        excelDeviceType.setScale("10");
        excelDeviceType.setType(country.getType());
        excelDeviceType.setAddTime(new Date());
        return excelDeviceType;
    }

    //添加项目
    private ExcelProject addProject(Excel country) {
        ExcelProject excelProject = new ExcelProject();
        excelProject.setProjectNo(country.getProjectNo());
        excelProject.setState("1");
        excelProject.setProjectName(country.getProjectName());
        excelProject.setProjectAdd(country.getProjectAddress());
        excelProject.setStartTime(country.getStartTime());
        switch (country.getMonitorUnit()){
            case "107地质队":
                excelProject.setMonitorUnit(70);
                break;
            case "205地质队":
                excelProject.setMonitorUnit(71);
                break;
            case "四川省地质矿产勘查开发局405地质队":
                excelProject.setMonitorUnit(72);
                break;
            case "重庆华地资环科技有限公司":
                excelProject.setMonitorUnit(74);
                break;
        }
        excelProject.setProjectLon(country.getProjectLon());
        excelProject.setProjectLat(country.getProjectLat());
        excelProject.setProjectMan(country.getProjectMan());
        excelProject.setProjectManPhone(country.getProjectManPhone());
        excelProject.setDisNo(country.getDisNo());
        excelProject.setUserId(country.getUserId());
        return excelProject;
    }

    //添加灾害点
    private ExcelBasic addBasic(Excel country) {
        ExcelBasic excelBasic = new ExcelBasic();
        excelBasic.setDisNo(country.getDisNo());
        excelBasic.setDisName(country.getDisName());
        excelBasic.setDisType(country.getDisType());
        excelBasic.setDisasterName(country.getDisasterName());
        excelBasic.setDisasterLevel(country.getDangerLevel());
        return  excelBasic;
    }

    //添加设备
    private ExcelDevice addDevice(Excel country) {
        ExcelDevice excelDevice = new ExcelDevice();
        excelDevice.setAreaCode(country.getAreaName());
        excelDevice.setAddress(country.getAnwz());
        excelDevice.setDeviceName(country.getDeviceName());
        excelDevice.setDeviceNo(country.getDeviceNo());
        excelDevice.setDeviceType(country.getDeviceType());
        excelDevice.setDeviceTypeName(country.getDeviceName());
        excelDevice.setDeviceFactory(country.getDeviceFactory());
        excelDevice.setDeviceLon(country.getDeviceLon());
        excelDevice.setDeviceLat(country.getDeviceLat());
        excelDevice.setSimCard(country.getSimCard());
        excelDevice.setTransferType(country.getTransferType());
        excelDevice.setUserId(country.getUserId());
        return excelDevice;
    }

    //监测点
    private ExcelMonitor addMonitor(Excel country) {
        ExcelMonitor excelMonitor = new ExcelMonitor();
        excelMonitor.setMonitorNo(country.getSensorNo());
        excelMonitor.setMonitorName(country.getMonitorNo());
        excelMonitor.setPosition(country.getPosition());
        excelMonitor.setMonitorLon(country.getMonitorLon());
        excelMonitor.setMonitorLat(country.getMonitorLat());
        excelMonitor.setCreateTime(new Date());
        excelMonitor.setDeviceNo(country.getDeviceNo());
        excelMonitor.setProjetcNo(country.getProjectNo());
        return excelMonitor;
    }

    //添加传感器
    private ExcelSensor addSensor(Excel country) {
        ExcelSensor excelSensor = new ExcelSensor();
        excelSensor.setSensorType(country.getSensorType());
        excelSensor.setSensorName(country.getSensorName());
        excelSensor.setSensorNo(country.getSensorNo());
        excelSensor.setDeviceNo(country.getDeviceNo());
        return excelSensor;
    }

    //添加设备安装信息
    private ExcelDeviceInstall addDeviceInstall(Excel country) {
        ExcelDeviceInstall excelDeviceInstall = new ExcelDeviceInstall();
        excelDeviceInstall.setAnwz(country.getAnwz());
        excelDeviceInstall.setSgtj(country.getSgtj());
        excelDeviceInstall.setGdfs(country.getGdfs());
        excelDeviceInstall.setGdsm(country.getGdsm());
        excelDeviceInstall.setYxtj(country.getYxtj());
        excelDeviceInstall.setCgtj(country.getCgtj());
        excelDeviceInstall.setYdxh(country.getYdxh());
        excelDeviceInstall.setGprsXh(country.getGprsXh());
        excelDeviceInstall.setDx3g(country.getDx3g());
        excelDeviceInstall.setSlqk(country.getSlqk());
        excelDeviceInstall.setSgrq(country.getSgrq());
        excelDeviceInstall.setJgrq(country.getJgrq());
        excelDeviceInstall.setSgry(country.getSgry());
        excelDeviceInstall.setJcry(country.getJcry());
        excelDeviceInstall.setYsry(country.getYsry());
        excelDeviceInstall.setWgjc(country.getWgjc());
        excelDeviceInstall.setLgaz(country.getLgaz());
        excelDeviceInstall.setTyngd(country.getTyngd());
        excelDeviceInstall.setSjcj(country.getSjcj());
        excelDeviceInstall.setSjcs(country.getSjcs());
        excelDeviceInstall.setAzdw(country.getAzdw());
        excelDeviceInstall.setRemark(country.getRemark());
        excelDeviceInstall.setDeviceNo(country.getDeviceNo());
        return excelDeviceInstall;
    }

    //读取excel
    @SuppressWarnings("resource")
    public static Workbook readExcel(String filePath){
        Workbook wb = null;
        if(filePath==null){
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));//文件后缀名
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            //如果文件后缀名为xls
            if(".xls".equals(extString)){
                return wb = new HSSFWorkbook(is);
            }//如果文件后缀名为xlsx
            else if(".xlsx".equals(extString)){
                return wb = new XSSFWorkbook(is);
            }else{
                return wb = null;
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }
    @SuppressWarnings("deprecation")
    public static Object getCellFormatValue(Cell cell){
        Object cellValue = null;
        if(cell!=null){
            //判断cell类型
            switch(cell.getCellType()){
                case Cell.CELL_TYPE_NUMERIC:{
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                }
                case Cell.CELL_TYPE_FORMULA:{
                    //判断cell是否为日期格式
                    if(DateUtil.isCellDateFormatted(cell)){
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    }else{
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING:{
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        }else{
            cellValue = "";
        }
        return cellValue;
    }

    //导出南江离线设备
    @GetMapping("export")
    public void export(HttpServletResponse response,String userName){
        //  创建一个工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        //  创建一个工作表
        HSSFSheet sheet = wb.createSheet();

        //  创建字体
        HSSFFont font1 = wb.createFont();
        HSSFFont font2 = wb.createFont();
        font1.setFontHeightInPoints((short) 14);
        font1.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
        font2.setFontHeightInPoints((short) 12);
        font2.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
        //  创建单元格样式
        HSSFCellStyle css1 = wb.createCellStyle();
        HSSFCellStyle css2 = wb.createCellStyle();
        HSSFDataFormat df = wb.createDataFormat();
        //  设置单元格字体及格式
        css1.setFont(font1);
        css1.setDataFormat(df.getFormat("#,##0.0"));
        css2.setFont(font2);
        css2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
        int userId = excelMapper.selectIdByUserName(userName);
        List<Map<String,Object>> a = excelMapper.export(userId);
        //  创建行
        int i = 1;
        for(Map<String,Object> b : a){
            HSSFRow title = sheet.createRow(0);
            title.createCell(0).setCellValue("设备编号");
            title.createCell(1).setCellValue("设备名称");
            title.createCell(2).setCellValue("监测点编号");
            title.createCell(3).setCellValue("设备类型");
            HSSFRow row = sheet.createRow(i);
            for (int j = 0; j < 4; j = j + 4) {
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(b.get("device_no").toString());

                HSSFCell cell2 = row.createCell(j+1);
                cell2.setCellValue(b.get("device_name").toString());

                HSSFCell cell3 = row.createCell(j+2);
                cell3.setCellValue(b.get("monitor_no").toString());

                HSSFCell cell4 = row.createCell(j+3);
                cell4.setCellValue(b.get("static_name").toString());
            }
            i++;
        }

        //  写文件
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream("E:/lx.xls");
            wb.write(fos);
            fos.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e){
            e.printStackTrace();
        }
        downloadExcel(response,null);
    }

    public void downloadExcel(HttpServletResponse response, HttpServletRequest request) {
        //方法一：直接下载路径下的文件模板
        try {
            //获取要下载的模板名称
            String fileName = "lx.xls";
            //设置要下载的文件的名称
            response.setHeader("Content-disposition", "attachment;fileName=" + fileName);
            //通知客服文件的MIME类型
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            //获取文件的路径
            String filePath = "E://"+fileName;
            FileInputStream input = new FileInputStream(filePath);
            OutputStream out = response.getOutputStream();
            byte[] b = new byte[2048];
            int len;
            while ((len = input.read(b)) != -1) {
                out.write(b, 0, len);
            }
            //修正 Excel在“xxx.xlsx”中发现不可读取的内容。是否恢复此工作薄的内容？如果信任此工作簿的来源，请点击"是"
            response.setHeader("Content-Length", String.valueOf(input.getChannel().size()));
            input.close();
            //return Response.ok("应用导入模板下载完成");
        } catch (Exception ex) {
//            logger.error("getApplicationTemplate :", ex);
            //return Response.ok("应用导入模板下载失败！");
        }
    }
}
